﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace perpus02
{
    public partial class Form1 : Form
    {
        //SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Abahe\Documents\Visual Studio 2010\Projects\perpus02\perpus02\Database1.mdf;Integrated Security=True;User Instance=True");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader dr;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (txtId.Text != "" & txtName.Text != "")
            {
                cn.Open();
                cmd.CommandText = "insert into info (id,name) values ('"+txtId.Text+"','"+txtName.Text+"')";
                cmd.ExecuteNonQuery();
                cmd.Clone();
                MessageBox.Show("Recored Inserted!", "Programming coy");
                cn.Close();
                txtId.Text = "";
                txtName.Text = "";
                loadList();
            }
        }

        private void loadList()
        {
            listBox1.Items.Clear();
            listBox2.Items.Clear();
            cn.Open();
            cmd.CommandText = "select * from info";
            dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    listBox1.Items.Add(dr[0].ToString());
                    listBox2.Items.Add(dr[1].ToString());
                }
            }
            cn.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cmd.Connection = cn;
            loadList();
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBox l = sender as ListBox;
            if (l.SelectedIndex != -1)
            {
                listBox1.SelectedIndex = l.SelectedIndex;
                listBox2.SelectedIndex = l.SelectedIndex;
                txtId.Text = listBox1.SelectedItem.ToString();
                txtName.Text = listBox2.SelectedItem.ToString();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (txtId.Text != "" & txtName.Text != "")
            {
                cn.Open();
                cmd.CommandText = "delete from info where id='" + txtId.Text + "' and name='" + txtName.Text +"'";
                cmd.ExecuteNonQuery();
                cn.Close();
                MessageBox.Show("Record Deleted", "oi");
                loadList();
                txtId.Text = "";
                txtName.Text = "";
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (txtId.Text != "" & txtName.Text != "")
            {
                cn.Open();
                cmd.CommandText = "update info set name='" + txtName.Text + "' WHERE id='" + 
                    txtId.Text + "' ";
                cmd.ExecuteNonQuery();
                cn.Close();
                MessageBox.Show("Record updated", "oi");
                loadList();
                txtId.Text = "";
                txtName.Text = "";
            }
        }
    }
}
